package com.huaxia.dao.goOn;

import com.huaxia.pojo.goOn.BranchGoOn;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 17:14
 */

public interface BranchGoOnMapper {

    @Select("select t1.branchsname,\n" +
            "nvl(t2.branch13jMonPaid,0)branch13jMonPaid,nvl(t2.branch13jMonWritten,0)branch13jMonWritten,round(decode(nvl(t2.branch13jMonWritten,0),0,-9999,t2.branch13jMonPaid*100/t2.branch13jMonWritten),1) branch13jMonYield, \n" +
            "nvl(t3.branch13jYearPaid,0)branch13jYearPaid,nvl(t3.branch13jYearWritten,0)branch13jYearWritten,round(decode(nvl(t3.branch13jYearWritten,0),0,-9999,t3.branch13jYearPaid*100/t3.branch13jYearWritten),1) branch13jYearYield,\n" +
            "nvl(t4.branch25jMonPaid,0)branch25jMonPaid,nvl(t4.branch25jMonWritten,0)branch25jMonWritten,round(decode(nvl(t4.branch25jMonWritten,0),0,-9999,t4.branch25jMonPaid*100/t4.branch25jMonWritten),1) branch25jMonYield, \n" +
            "nvl(t5.branch25jYearPaid,0)branch25jYearPaid,nvl(t5.branch25jYearWritten,0)branch25jYearWritten,round(decode(nvl(t5.branch25jYearWritten,0),0,-9999,t5.branch25jYearPaid*100/t5.branch25jYearWritten),1) branch25jYearYield\n" +
            "from SD_BRANCH t1 left join\n" +
            "(select substr(t.managecom, 0, 6)branchcode, sum(t.paid_prem)branch13jMonPaid, sum(t.written_prem)branch13jMonWritten from FACT_ALL_PSTPREM_YEAR13J t\n" +
            "    where stat_date=to_char(sysdate,'yyyymm') and t.salechnl_flag in('02','05') group by substr(t.managecom, 0, 6)\n" +
            ")t2 on t1.branchCode=t2.branchCode left join\n" +
            "(select substr(t.managecom, 0, 6)branchcode, sum(t.paid_prem)branch13jYearPaid, sum(t.written_prem)branch13jYearWritten from FACT_ALL_PSTPREM_YEAR13J_cum t\n" +
            "    where substr(stat_date,0,4)=to_char(sysdate,'yyyy') and stat_date<=to_char(sysdate,'yyyymm')  and t.salechnl_flag in('02','05') group by substr(t.managecom, 0, 6)\n" +
            ")t3 on t2.branchCode=t3.branchCode left join\n" +
            "(select substr(t.managecom, 0, 6)branchcode, sum(t.paid_prem)branch25jMonPaid, sum(t.written_prem)branch25jMonWritten from FACT_ALL_PSTPREM_YEAR25J t\n" +
            "    where stat_date=to_char(sysdate,'yyyymm')  and t.salechnl_flag in('02','05') group by substr(t.managecom, 0, 6)\n" +
            ")t4 on t3.branchCode=t4.branchCode left join\n" +
            "(select substr(t.managecom, 0, 6)branchcode, sum(t.paid_prem)branch25jYearPaid, sum(t.written_prem)branch25jYearWritten from FACT_ALL_PSTPREM_YEAR25J_cum t\n" +
            "    where substr(stat_date,0,4)=to_char(sysdate,'yyyy') and stat_date<=to_char(sysdate,'yyyymm') and t.salechnl_flag in('02','05') group by substr(t.managecom, 0, 6)\n" +
            ")t5 on t4.branchCode=t5.branchCode")
    List<BranchGoOn> getBranchGoOn();
    
    
    
    }
